<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <link rel="stylesheet" href="php-txt-db-api.css" type="text/css">
  <title>SQL Syntax (Einschr&auml;nkungen f&uuml;r Txt-Db-Api)</title>
</head>
    <body>
<div align="center">    
<h1 align="left"> SQL Syntax of the Txt-Db-Api (Limitations)</h1>
   
<h2 align="left">&nbsp;</h2>
   
<h2 align="left">Abstract</h2>
   
<p align="left">The Txt-Db-Api does not support all SQL keywords/statements
     nor the SQL Standard.<br>
     On this Page the sql syntax for the txt-db-api and its limitations are
listed.<br>
     Everything in [] is optional, "..." means that any more elements   
  can follow, <br>
     | means OR (AND|OR =&gt; you can put AND or OR at this place).</p>
   
<p align="left"><b>As in Standard SQL: Do always enclose values for str (String)
     fields in quotes (' ' or " ") !</b></p>
   
<p align="left">&nbsp;</p>
   
<h2 align="left">SELECT</h2>
   
<h3 align="left">Syntax</h3>
   
<div align="left">      
<pre align="left">SELECT [DISTINCT]<br>[function(][table_name.]column[)] [AS alias_name] [, [function(][table_name].column[)] [AS alias_name] , ...]  <br>FROM tablex [AS table_alias] [, tabley [AS table_alias], ... ] <br>[WHERE column=value AND|OR column=column AND|OR ...] <br>[GROUP BY column, ...]<br>[ORDER BY column [ASC | DESC] ,...]<br>[LIMIT [offset,]rows [;]      <br></pre>
     
<h3 align="left">Supported</h3>
     
<p>- look at the syntax...<br>
     </p>
     
<p>- Instead of column names (<font face="Courier New, Courier, mono">column</font>
       in the syntax above) you may also use direct values.<br>
       &nbsp;&nbsp;This can be numbers or strings in ' ' or " ", for    
   example <font face="Courier New, Courier, mono">SELECT 1234, 'bla', name
       FROM people;</font> </p>
     
<p>- The following functions: UNIX_TIMESTAMP(), MD5(column), NOW(), ABS(column),
       LCASE(column), UCASE(column), LOWER(column), UPPER(column), EVAL(column
*-+/ value|column...)<br>
       - The functions in the line above are also supported in the WHERE,
ORDER        BY and GROUP BY part.<br>
       - The following grouping functions: MAX(column), MIN(column), COUNT(column),
       SUM(column), AVG(column)<br>
       - You can also use direct values as parameters for the functions,
instead        of columns, for example md5('bla')<br>
     </p>
     
<p>- * instead of field names<br>
       - table.*, combinations are also allowed, for example: SELECT table1.*,
       people.Name, table3.* ...</p>
     
<p>- Aliases for Tables</p>
     
<p>- In the WHERE part, the following operators are supported: =,&gt;,&lt;,&gt;=,&lt;=,
       !=, &lt;&gt;, LIKE (you can use % or _ as wildcards)<br>
       - In the WHERE part, you can use field=value, value=value or field=field
       conditions <br>
       &nbsp;&nbsp;(to query tables linked, e.g. "person.typ_id = types.id",&nbsp;for
       field=field conditions all the above <br>
       &nbsp;&nbsp;listed operators can be used)<br>
       - In the WHERE part you can use countless AND's and OR's<br>
       - In the WHERE part brackets can be used, and also be nested<br>
       <br>
       - DISTINCT: only unique rows are returned<br>
       - GROUP BY: the result will be grouped by this columns<br>
       <br>
       - LIMIT: only <i>rows </i>Rows are returned, starting with<i> offset
</i>(The        first Row has offset 0!) <br>
       <br>
       - When using DISTINCT and GROUP BY always the first value is taken<br>
       &nbsp;&nbsp;Example: "SELECT time, ip FROM log GROUP BY time"</p>
     
<table width="463" border="0">
       <tbody>
    <tr>
          <td width="160">time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;ip<br>
           10348395 | 192.168.0.1<br>
           10348395 | 192.168.0.3</td>
         <td width="34">            
      <div align="center">&gt;</div>
         </td>
         <td width="255">time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;ip<br>
           10348395 | 192.168.0.1</td>
       </tr>
     
  </tbody>
</table>
     
<h3 align="left">Not supported</h3>
     
<p>- Appending of Fields like <i>SELECT name &amp; " " &amp; prename    
   AS whole_name</i> or<i><br>
       &nbsp;&nbsp;SELECT name + prename AS whole_name</i><br>
       - Everything not found under Syntax or supported !</p>
     
<h3>Examples</h3>
     
<p>SELECT prename, person.name, types.typ AS person_typ FROM person, types<br>
       WHERE person.typ_id = types.id AND (person.Nr&gt;=10 OR person.prename='Hans')
       <br>
       ORDER BY prename</p>
     
<p>SELECT COUNT(m.name), AVG(m.age) AS avgAge, c.name FROM members AS m,
course        AS c<br>
       WHERE m.course_nr=course.id GROUP BY c.name;</p>
     
<p>SELECT md5('bla');<br>
     </p>
     
<h3>Tip's</h3>
     
<p><b>ATTENTION: </b>Before and after each OR or AND must be a Space ("  
     "). This is<br>
       requied for the parser, that can distinguish between field names containing<br>
       OR or AND as part of it, or real OR or AND's. The same applies for
LIKE.<br>
       Examples:<br>
       Wrong: Name='Test'OR Nr&gt;20<br>
       Right: Name='Test' OR Nr&gt;20</p>
     
<p><b>ATTENTION: </b>Use LIKE only if it's really necessary. Normal String
       compares are much<br>
       faster then LIKE. For example, if you want to query all records where
the        name is <b>exactly</b><br>
       'tom', do it like this: SELECT * FROM table WHERE name='tom'<br>
       AND NOT: SELECT * FROM table WHERE name LIKE 'tom'<br>
       AND ALSO NOT: SELECT * FROM table WHERE name LIKE '%tom%'</p>
     
<p>&nbsp;</p>
     
<h2 align="left">INSERT</h2>
     
<h3 align="left">Syntax</h3>
     
<div align="left">        
<pre align="left">INSERT [INTO] table [(column1, column2, column_n, ...)] <br>VALUES  (  [function(] value [)] [, [function(] value2 [)], value_n, ...]) [;]<br>	or<br>INSERT [INTO] table<br>SET column=[function(]value[)] [,column2= [function(]value2[)], column_n= [function(] value_n [)] , ...] [;]<br></pre>
       
<h3 align="left">Supported</h3>
       
<p>- Look at syntax<br>
         - The following functions: UNIX_TIMESTAMP(), MD5(value), NOW(),
ABS(value),          LCASE(value), UCASE(value), LOWER(value), UPPER(value),
EVAL(column *-+/ value|column...)<br>
         - Only values can be used as parameters for the functions in INSERT
(an          NOT columns)</p>
       
<h3 align="left">Not supported</h3>
       - INSERT with SELECT<br>
       - Insert more then one Record per SQL Statement<br>
       - Grouping functions MAX(), MIN() etc.<br>
       - Everything not under Syntax        
<h3 align="left">Examples</h3>
       
<p>INSERT INTO person (prename, name) VALUES ('hans','meier');</p>
       
<p>INSERT INTO passwords (user, password) VALUES ( 'user1', md5('mypassword')
         );</p>
       
<p>INSERT INTO person SET prename=upper('hans'), age=abs(-55), added=NOW()
         ; </p>
       
<p>&nbsp;</p>
       
<h2 align="left">DELETE</h2>
       
<h3 align="left">Syntax</h3>
       
<div align="left">          
<pre align="left">DELETE FROM table [WHERE &lt;same as in SELECT&gt;] [;]<br></pre>
         
<h3 align="left"><br>
           Supported</h3>
         
<p>- Look at Syntax<br>
           - Deleting the whole Table (drop the WHERE part, for example:
"DELETE            FROM tableXY;")<br>
           - Same WHERE Syntax as in SELECT (functions are also supported!)<br>
         </p>
         
<h3 align="left">Not supported</h3>
         
<p>- LIMIT<br>
           - Same restrictions for the WHERE part as by SELECT<br>
           - Everything not found in the Syntax above..</p>
         
<h3>Examples</h3>
         
<p>DELETE FROM person WHERE prename='Hans';</p>
         
<p>DELETE FROM person WHERE password=md5('mypw') AND abs(age)=abs(-5);<br>
         </p>
         
<p>&nbsp; </p>
         
<h2 align="left">UPDATE</h2>
         
<h3 align="left">Syntax</h3>
         
<div align="left">            
<pre align="left">UPDATE table SET column=[function(]value[)] [,column2=[function(]value2[)], column_n=[function(]value_n[)], ...] <br>[WHERE &lt;same as in SELECT&gt;] [;]<br></pre>
           
<h3 align="left"><br>
             Supported</h3>
           
<p>- Look at Syntax..<br>
             - The following functions for the values: UNIX_TIMESTAMP(),
MD5(column              or value), NOW(), ABS(column or value), LCASE(column
or value), <br>
             &nbsp;&nbsp;UCASE(column or value), LOWER(column or value),
UPPER(column              or value)<br>
             - Update the whole Table (drop the WHERE part)<br>
             - Assign the value of one column to another<br>
             - Same syntax for the WHERE part as SELECT <br>
           </p>
           
<h3 align="left">Not supported</h3>
           
<p>- LIMIT<br>
             - ORDER BY<br>
             - Everything not found in the Syntax above..</p>
           
<h3>Examples</h3>
           
<p>UPDATE person SET prename='Hans2000' WHERE prename='Hans';<br>
             <br>
             UPDATE person SET prename=name WHERE prename='Hans'; <br>
             <br>
             UPDATE person SET password=md5(password);<br>
             <br>
             UPDATE person SET age=abs(-20), name=upper(name); </p>
         </div>
         
<p>&nbsp;</p>
       </div>
       
<h2 align="left"><a name="createTable"></a>CREATE TABLE</h2>
       
<h3 align="left">Syntax</h3>
       
<div align="left">          
<pre align="left">CREATE TABLE (column_name  type [DEFAULT 'defaultValue'] [, column_name2  type2 [DEFAULT 'defaultValue'] , ... ]) [;]<br></pre>
         
<h3 align="left"><br>
           Supported</h3>
         
<p>- Look at Syntax<br>
           - DEFAULT (Enclose the Default-Value in ' ')<br>
           - ONLY the following Synatx: (column_name type , column_name type
,            ....), and the Keyword DEFAULT, and thats all !<br>
           - If you specify an Default-Value for an inc column, the first
records            inc values will be set to DefaultValue+1,<br>
           &nbsp;&nbsp;and for all other Records the Default-Value is ignored.
           <br>
         </p>
         
<h3 align="left">Not supported</h3>
         
<p> - Everything not found under Syntax !!<br>
           - SQL Standard types, <b>type can only be inc (Autoincrement),
int (Number)            or str (String,Text).</b></p>
         
<h3>Example</h3>
         
<p>CREATE TABLE people (id inc, prename str DEFAULT 'john', name str);</p>
         
<p>&nbsp;</p>
         
<p>&nbsp;</p>
         
<h2 align="left">DROP TABLE</h2>
         
<h3 align="left">Syntax</h3>
         
<div align="left">            
<pre align="left">DROP TABLE table1 [, table2, ...][;]</pre>
           
<h3 align="left"><br>
             Supported</h3>
           
<p>- Look at Syntax<br>
             - Drop 1 or more tables in one statement<br>
           </p>
           
<h3 align="left">Not supported</h3>
           
<p> - Everything not found under Syntax !!<br>
           </p>
           
<h3>Example</h3>
           
<p>DTOP TABLE people;</p>
           
<p>&nbsp;</p>
         </div>
         
<h2 align="left">LIST TABLES</h2>
         
<h3 align="left">Syntax</h3>
         
<div align="left">            
<pre align="left">LIST TABLES [WHERE ...] <br>[GROUP BY spalten_name, ...]<br>[ORDER BY spalten_name [ASC | DESC] ,...] <br>[LIMIT [offset,]anzahl] [;]      </pre>
           
<h3 align="left"><br>
             Supported</h3>
           
<p>- Look at Syntax<br>
             - Returns a ResultSet Object containing one Column with the
name "table",              <br>
             &nbsp;&nbsp;which contains all Table names of the Database as
String.<br>
             - For the WHERE, GROUP BY, ORDER BY and LIMIT syntax see SELECT<br>
             <br>
           </p>
           
<h3 align="left">Not Supported</h3>
           
<p> - Everything not found under Syntax !!<br>
             - Functions<br>
           </p>
           
<h3>Example</h3>
           
<p>LIST TABLES;</p>
         </div>
         
<p>&nbsp;</p>
         
<h2 align="left">CREATE DATABASE</h2>
         
<h3 align="left">Syntax</h3>
         
<div align="left">            
<pre align="left">CREATE DATABASE database_name [;]</pre>
           
<h3 align="left"><br>
             Supported</h3>
           
<p>- Look at Syntax<br>
             - Only one Database can be created in one Statement.<br>
             - This Statement should only be executed on a ROOT_DATABASE
Database!<br>
             &nbsp;&nbsp;You can get an instance of it with a $db=new Database(ROOT_DATABASE);
             call. <br>
             &nbsp;&nbsp;ROOT_DATABASE is a defined constant.<br>
             <br>
           </p>
           
<h3 align="left">Not Supported</h3>
           
<p> - Everything not found under Syntax !!<br>
           </p>
           
<h3>Example</h3>
           
<p>CREATE DATABASE myDatabase;</p>
         </div>
         
<p>&nbsp;</p>
         
<h2 align="left">DROP DATABASE</h2>
         
<h3 align="left">Syntax</h3>
         
<div align="left">            
<pre align="left">DROP DATABASE database_name [;]</pre>
           
<h3 align="left"><br>
             Supported</h3>
           
<p>- Look at Syntax<br>
             - Only one Database can be deleted in one Statement.<br>
             - This Statement should only be executed on a ROOT_DATABASE
Database!<br>
             &nbsp;&nbsp;You can get an instance of it with a $db=new Database(ROOT_DATABASE);
             call. <br>
             &nbsp;&nbsp;ROOT_DATABASE is a defined constant.<br>
           </p>
           
<h3 align="left">Not Supported</h3>
           
<p> - Everything not found under Syntax !!<br>
           </p>
           
<h3>Example</h3>
           
<p>DROP DATABASE myDatabase;</p>
         </div>
         
<p>&nbsp;</p>
         </div>
            </div>
   </div>
 </div>
 <br>
</body>
</html>
